package com.googlecode.garbagecan.dbtest.derby;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

//Driver:org.apache.derby.jdbc.ClientDriver
//URL:jdbc:derby://localhost:1527/MyDBTest;create=true
//Username/Password:app/app
public class TestDerby {
    
    public static void main(String[] args) throws Exception {
        String s1 = "securityMechanism";
        String s2 = "0x03";
        Properties prop = new Properties();
        prop.setProperty(s1,s2);
//        prop.setProperty("","");
//        prop.setProperty("","");
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        String url = "jdbc:derby://localhost:1527/app;user=app;password=app";
        Connection conn = DriverManager.getConnection(url,prop);
        String sql = " select * from t1";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        System.out.println(rs.getRow());
        rs.close();
        conn.close();
    }
    
    public static void testCreate() throws Exception {
        String sql = "create table test1(";
        sql += " t1 integer, ";
        sql += " t2 varchar(50), ";
        sql += " t3 varchar(50), ";
        sql += " t4 varchar(50), ";
        sql += " t5 varchar(50), PRIMARY KEY(t1))";
        System.out.println(sql);
//	Connection conn = getConnection();
//	PreparedStatement ps = conn.prepareStatement(sql);
//	ps.executeUpdate(sql);
//	conn.close();
    }
    public static void testBatch() throws Exception {
        Connection conn = getConnection();
        PreparedStatement ps ;
        String sql ;
        
//        sql = "delete from test1";
//        ps = conn.prepareStatement(sql);
//        ps.executeUpdate();
//        conn.commit();
        
        sql = "insert into test1(t1,t2,t3,t4,t5)values(?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        
        int length = 10;
        for(int i = 0 ; i < length ; i++) {
            ps.setObject(1,i);
            ps.setObject(2,"" + i);
            ps.setObject(3,"" + i);
            ps.setObject(4,"" + i);
            if( i != length - 1) {
                ps.setObject(5,"" + i);
            } else {
                ps.setNull(5, Types.VARCHAR);
            }
            ps.addBatch();
        }
        ps.executeBatch();
        ps.clearBatch();
        ps.close();
        conn.commit();
        conn.close();
    }
    
    public static void testBatch2() throws Exception {
        try {
            Connection conn = getConnection();
            PreparedStatement ps ;
            String sql ;
            sql = "INSERT INTO TASK_ATTRIBUTES(TIME_STAMP,TIME_STAMP_GMT,TASK_ID,SESSION_ID, APP_NAME ,TASK_STATE,SUBMIT_TIME,START_TIME, END_TIME,EXEC_HOST,INPUT_SIZE ,OUTPUT_SIZE,SERVICE_PID ,RERUN_COUNT)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, new Timestamp(parseToDate("2007-02-06 17:35:30.109").getTime()));
            ps.setObject(2, 1170754530109L);
            ps.setObject(3, 28070);
            ps.setObject(4, 1);
            ps.setObject(5, "symping");
            ps.setObject(6, "Done");
            ps.setObject(7, new Timestamp(parseToDate("2007-02-06 17:35:30.109").getTime()));
            ps.setObject(8, new Timestamp(parseToDate("2007-02-06 17:35:30.18").getTime()));
            ps.setObject(9, new Timestamp(parseToDate("2007-02-06 17:35:30.239").getTime()));
            ps.setObject(10, "xa39");
            ps.setObject(11, 102);
            ps.setObject(12, 192);
            ps.setObject(13, 17145);
            ps.setObject(14, 0);
            ps.addBatch();
            ps.executeBatch();
            ps.clearBatch();
            ps.close();
            conn.commit();
            conn.close();
        } catch(SQLException ex) {
            System.out.println(ex.getSQLState());
            ex.printStackTrace();
            
            SQLException e ;
            e = ex.getNextException();
//            System.out.println(e.getSQLState());
//            e.printStackTrace();
            
            e = ex.getNextException();
            e = ex.getNextException();
            e = ex.getNextException();
            e = ex.getNextException();
            System.out.println(e.getSQLState());
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        //String url = "jdbc:derby://localhost:1527/app;create=true";
        String url = "jdbc:derby://172.17.33.31:1527/app";
        Connection conn = DriverManager.getConnection(url,"app","app");
        return conn;
    }
    
    public static Date parseToDate(String s) throws ParseException {
	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
	Date date = df.parse(s);
	return date;
    }
}
